﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;

namespace JobDescOnline.Presentation.Models
{
    public static class JobManagerialCompetencies
    {
        public static JobDescDBEntities1 db = new JobDescDBEntities1();
        public static dynamic RetrieveByFilterAndPage(List<FilterData> filters, int page, int pagesize, string sort, string direction)
        {
            var _temp = from j in db.JobManagerialCompetencies
                        join jt in db.JobTitles on j.JobTitleID equals jt.ID
                        join c in db.Competencies on j.CompetencyID equals c.ID
                        join l in db.LevelCompetencies on j.LevelCompetencyID equals l.ID
                        select new JobTitleManagerialCompetency
                        {
                            ID = j.ID,
                            Rowstatus = j.Rowstatus,
                            JobTitleName = jt.Description,
                            Competency = c.Description,
                            Level = l.Level,
                            CreatedBy = j.CreatedBy,
                            CreatedOn = j.CreatedOn,
                            LastModifiedBy = j.LastModifiedBy,
                            LastModifiedOn = j.LastModifiedOn
                        };

            if (direction.ToLower() == "asc")
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderBy(j => j.ID); break;
                    case "JobTitle": _temp = _temp.OrderBy(j => j.JobTitleName); break;
                    case "Competency": _temp = _temp.OrderBy(j => j.Competency); break;
                }
            }
            else
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderByDescending(j => j.ID); break;
                    case "JobTitle": _temp = _temp.OrderByDescending(j => j.JobTitleName); break;
                    case "Competency": _temp = _temp.OrderByDescending(j => j.Competency); break;
                }
            }

            if (filters.Count > 0)
            {
                foreach (FilterData filter in filters)
                {
                    FilterData copy = filter;
                    switch (filter.column)
                    {
                        case "JobTitle": _temp = _temp.Where(j => j.JobTitleName.Contains(copy.value)); break;
                        case "Competency": _temp = _temp.Where(j => j.Competency.Contains(copy.value)); break;
                    }
                }
            }

            return _temp.Skip((page - 1) * pagesize).Take(pagesize).ToList();
        }

        public static dynamic RetrieveByFilterAndPage(int page, int pagesize, string sort, string direction)
        {
            var _temp = from j in db.JobManagerialCompetencies
                        join jt in db.JobTitles on j.JobTitleID equals jt.ID
                        join c in db.Competencies on j.CompetencyID equals c.ID
                        join l in db.LevelCompetencies on j.LevelCompetencyID equals l.ID
                        select new JobTitleManagerialCompetency
                        {
                            ID = j.ID,
                            Rowstatus = j.Rowstatus,
                            JobTitleName = jt.Description,
                            Competency = c.Description,
                            Level = l.Level,
                            CreatedBy = j.CreatedBy,
                            CreatedOn = j.CreatedOn,
                            LastModifiedBy = j.LastModifiedBy,
                            LastModifiedOn = j.LastModifiedOn
                        };

            if (direction.ToLower() == "asc")
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderBy(j => j.ID); break;
                    case "JobTitle": _temp = _temp.OrderBy(j => j.JobTitleName); break;
                    case "Competency": _temp = _temp.OrderBy(j => j.Competency); break;
                }
            }
            else
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderByDescending(j => j.ID); break;
                    case "JobTitle": _temp = _temp.OrderByDescending(j => j.JobTitleName); break;
                    case "Competency": _temp = _temp.OrderByDescending(j => j.Competency); break;
                }
            }

            return _temp.Skip((page - 1) * pagesize).Take(pagesize).ToList();
        }

        public static int RetrieveByFilter(List<FilterData> filters)
        {
            var _temp = from j in db.JobManagerialCompetencies
                        join jt in db.JobTitles on j.JobTitleID equals jt.ID
                        join c in db.Competencies on j.CompetencyID equals c.ID
                        join l in db.LevelCompetencies on j.LevelCompetencyID equals l.ID
                        select new JobTitleManagerialCompetency
                        {
                            ID = j.ID,
                            Rowstatus = j.Rowstatus,
                            JobTitleName = jt.Description,
                            Competency = c.Description,
                            Level = j.LevelCompetencyID,
                            CreatedBy = j.CreatedBy,
                            CreatedOn = j.CreatedOn,
                            LastModifiedBy = j.LastModifiedBy,
                            LastModifiedOn = j.LastModifiedOn
                        };

            if (filters.Count > 0)
            {
                foreach (FilterData filter in filters)
                {
                    FilterData copy = filter;
                    switch (filter.column)
                    {
                        case "JobTitle": _temp = _temp.Where(j => j.JobTitleName.Contains(copy.value)); break;
                        case "Competency": _temp = _temp.Where(j => j.Competency.Contains(copy.value)); break;
                    }
                }
            }

            return _temp.ToList().Count;
        }

        public static int RetrieveByFilter()
        {
            var list = db.JobCompetencies.ToList();
            return list.Count;
        }
    }

    public class JobTitleManagerialCompetency
    {
        public int ID { get; set; }
        public bool Rowstatus { get; set; }
        public string JobTitleName { get; set; }
        public string Competency { get; set; }
        public Nullable<int> Level { get; set; }
        public string CreatedBy { get; set; }
        public Nullable<DateTime> CreatedOn { get; set; }
        public string LastModifiedBy { get; set; }
        public Nullable<DateTime> LastModifiedOn { get; set; }
    }
}